CREATE TABLE jms_ods.yl_oms_oms_waybill_three_years_his (
  input_time datetime NULL COMMENT '录入时间',
  pick_network_code varchar(70) NULL COMMENT '寄件网点code',
  waybill_no varchar(70) NULL COMMENT '运单号',
  SENDER_MOBILE_PHONE VARCHAR(30) NULL COMMENT '寄件人手机号',
  RECEIVER_TELPHONE          varchar(30)         comment  '收件人座机' ,                     --NVARCHAR2(30)
  id bigint(20) NULL COMMENT '主键id',
  delivery_time datetime NULL COMMENT '寄件时间',
  collect_staff_code varchar(70) NULL COMMENT '揽件业务员code',
  collect_staff_name varchar(110) NULL COMMENT '揽件业务员名称',
  ABNORMAL_REG_NETWORK_CODE varchar(30) null comment '揽件网点编码',
  ABNORMAL_REG_NETWORK_NAME varchar(60) null comment '揽件网点',
  dispatch_staff_code varchar(70) NULL COMMENT '派件业务员code',
  dispatch_staff_name varchar(110) NULL COMMENT '派件业务员名称',
  dispatch_time datetime NULL COMMENT '派件时间',
  dispatch_network_code varchar(70) NULL COMMENT '派件网点code',
  dispatch_network_name varchar(110) NULL COMMENT '派件网点名称',
  sign_network_code varchar(70) NULL COMMENT '签收网点code',
  sign_network_name varchar(110) NULL COMMENT '签收网点名称',
  sign_time datetime NULL COMMENT '签收时间',
  customer_code varchar(70) NULL COMMENT '客户编号code',
  customer_name varchar(190) NULL COMMENT '客户名称',
  SENDER_NAME varchar(190) null comment '寄件人姓名',
  sender_province_name varchar(70) NULL COMMENT '寄件省份名称',
  sender_city_name varchar(70) NULL COMMENT '寄件城市名称',
  sender_area_name varchar(110) NULL COMMENT '寄件区域名称',

  SENDER_TELPHONE VARCHAR(30) null comment '寄件人固机',
  sender_detailed_address varchar(1060) NULL COMMENT '寄件详细地址',
  pick_network_name varchar(110) NULL COMMENT '寄件网点名称',
  total_freight decimal(14,2) NULL COMMENT '总运费',
  ID_NO varchar(100) null comment '身份证号',
  PACKAGE_NUMBER smallint null comment '件数',
  GOODS_NAME varchar(300) null comment '物品名称',
  PACKAGE_INSIDE_CHARGE_WEIGHT decimal(14,2) null comment '内部计费重量',
  receiver_name varchar(190) NULL COMMENT '收件人姓名',
  RECEIVER_MOBILE_PHONE     varchar(30)            comment  '收件人手机号' ,                     --NVARCHAR2(30)

  receiver_province_name varchar(110) NULL COMMENT '收件省份名称',
  receiver_city_name varchar(110) NULL COMMENT '收件城市名称',
  receiver_area_name varchar(110) NULL COMMENT '收件区域名称',
  receiver_detailed_address varchar(1060) NULL COMMENT '收件详细地址',
  input_network_code varchar(110) NULL COMMENT '录入网点code',
  input_network_name varchar(110) NULL COMMENT '录入网点名称',
INSPECTION_METHOD  varchar(100) null comment '手机密文'

)ENGINE=OLAP
UNIQUE KEY(input_time, pick_network_code, waybill_no,SENDER_MOBILE_PHONE,RECEIVER_TELPHONE,id)
COMMENT '运单表'
PARTITION BY RANGE(input_time)
(

    START ('2021-02-01') END ('2024-02-06') EVERY (INTERVAL 1 day)
)

DISTRIBUTED BY HASH(pick_network_code) BUCKETS 32
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-1095',
'dynamic_partition.end' = '1',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '32',
'in_memory' = 'false',
'storage_format' = 'V2'
);